package DBLayer;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;

import ModelLayer.*;

public class DBLineItem implements IFDBGeneric<LineItem>{
	private Connection con;

	public DBLineItem()
	{
		con = DbConnection.getInstance().getDBcon();
	}
	
	
	
	public ArrayList<LineItem> getAll() {
		return  miscWhere("");
	}
	
	public ArrayList<LineItem> getAll_byInvoiceNo(int invoiceNo) {
		return  miscWhere("invoiceNo="+invoiceNo);
	}
	
	public LineItem find(int Prid) {
		return singleWhere("Prid = '"+Prid+"'");
	}
	
	public LineItem find(String name) {return null;	}
	
	

	public int delete(int invoiceNo) {
		int rc = -1;
		
		String query = "DELETE FROM LineItem WHERE invoiceNo = "+invoiceNo;
		
		try{
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			rc = stmt.executeUpdate(query);
			stmt.close();
		}
		catch(Exception ex){
			System.err.println("Couldn't delete the LineItem with Product ID "+invoiceNo);
		}
		
		
		return rc;
	}
	
	
	public int insert(LineItem lineItem) throws Exception {
	
		int rc = -1;
		
		String query1 = "INSERT INTO LineItem (Prid, InvoiceNo, Quantity) VALUES ('"+lineItem.getPrid()+"','"+lineItem.getInvoiceNo()+"','"+lineItem.getQuantity()+"',')";
		
		try{
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			rc = stmt.executeUpdate(query1);
			
			stmt.close();
		}
		catch(Exception ex){
			System.err.println("Couldn't insert the LineItem with Product If "+lineItem.getPrid());
		}
		
		return rc;
	}

	
	public int update(LineItem lineItem) {
		int rc = -1;
		
		String query1 =  "UPDATE LineItem SET Prid = "+lineItem.getPrid() +", invoiceNo="+lineItem.getInvoiceNo()+", quantity = "+lineItem.getQuantity();
		
		try{
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			rc = stmt.executeUpdate(query1);
			
			stmt.close();
		}
		catch(Exception ex){
			System.err.println("Couldn't update the LineItem with Product ID "+lineItem.getPrid());
		}
		
		
		return rc;
	}
	
	
	
	
	
	
	
	
	private LineItem singleWhere(String wClause)
	{
		LineItem lineItem =null;
		
		ResultSet results;
		String query = buildQuery(wClause);
		
		
		try{
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			results = stmt.executeQuery(query);
			
			if(results.next())
				lineItem = buildLineItem(results);
			
			stmt.close();
		}
		catch(Exception ex){}
		
		return lineItem;
	}
	
	
	
	private ArrayList<LineItem> miscWhere(String wClause)
	{
		ArrayList<LineItem> lineItems = new ArrayList<>();
		
		ResultSet results;
		String query = buildQuery(wClause);
		
		try{
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			results = stmt.executeQuery(query);
			
			
			while(results.next())
			{
				LineItem lineItem = buildLineItem(results);
				lineItems.add(lineItem);
			}
			
			stmt.close();
			
		}
		catch(Exception ex){}
		
		
		
		
		return lineItems;
	}
	
	
	
	
	
	
	private LineItem buildLineItem(ResultSet result)
	{
		LineItem lineItem = new LineItem();
		
		try{	
			lineItem.setInvoiceNo(result.getInt("InvoiceNo"));
			lineItem.setPrid(result.getInt("Prid"));
			lineItem.setQuantity(result.getInt("Quantity"));
	
		}
		catch(Exception ex){
			System.err.println("COuld not build the lineItem");
		}
		
		return lineItem;
	}
	
	
	private String buildQuery(String wClause)
	{
		String query = "SELECT invoiceNo, Prid, Quantity FROM lineItem";
		
		if(wClause.length() > 0) query = query +" WHERE "+ wClause;
		
		return query;
	}



}
